﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Web.UI.WebControls;

namespace DBUtility
{
    /// <summary>
    /// Oledb 数据访问组件
    /// </summary>
    public class AccHelper
    {
        #region 初始化变量
        static OleDbConnection OledbConn;
        static OleDbCommand OledbCmd;
        static OleDbDataAdapter OledbDAdapter;
        static OleDbDataReader OledbDReader;
        static OleDbTransaction OledbTrans;

        static DataSet DS = new DataSet();
        static DataTable DT = new DataTable();
        #endregion


        #region 打开连接
        /// <summary>
        /// 打开连接
        /// </summary>
        public static void ConOpen()
        {
            try
            {
                if (OledbConn == null || OledbConn.State == ConnectionState.Closed)
                {
                    OledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|GBAMIS3.mdb;Persist Security Info=True");
                    //OledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\Web\\GBAMIS_3.0_Solution\\GBAMIS_3.0\\App_Data\\GBAMIS3.mdb;Persist Security Info=True");
                }

                OledbConn.Open();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion


        #region 关闭连接并释放资源
        /// <summary>
        /// 关闭连接并释放资源
        /// </summary>
        public static void Dispose()
        {
            try
            {
                if (OledbConn != null || OledbConn.State == ConnectionState.Open)
                {
                    OledbConn.Dispose();
                    OledbConn = null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion


        #region 获得DataSet
        /// <summary>
        /// 获得DataSet
        /// </summary>
        /// <param name="_SQLstr">SQL查询语句</param>
        /// <param name="_TableName">返回的数据集名称</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string _SQLstr, string _TableName)
        {
            try
            {
                DS.Clear();
                DS.Dispose();

                DS = new DataSet();

                ConOpen();
                OledbDAdapter = new OleDbDataAdapter(_SQLstr, OledbConn);
                OledbDAdapter.Fill(DS, _TableName);

                return DS;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }
        #endregion


        #region 获得DataTable
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="_SQLstr">SQL查询语句</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string _SQLstr)
        {
            try
            {
                DT = GetDataSet(_SQLstr, "DT").Tables["DT"];
                return DT;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion


        #region 获得DataReader
        /// <summary>
        /// 获得DataReader
        /// </summary>
        /// <param name="_SQLstr">SQL查询语句</param>
        /// <returns></returns>
        public static OleDbDataReader GetDataReader(string _SQLstr)
        {
            try
            {
                ConOpen();
                OledbCmd = new OleDbCommand(_SQLstr, OledbConn);
                OledbDReader = OledbCmd.ExecuteReader();

                return OledbDReader;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }
        #endregion


        #region 获得PagedDataSource
        /// <summary>
        /// 获得PagedDataSource
        /// </summary>
        /// <param name="_SQLstr"></param>
        /// <returns></returns>
        public static PagedDataSource GetPDS(string _SQLstr)
        {
            try
            {
                DS = GetDataSet(_SQLstr, "PDS");

                PagedDataSource PDS = new PagedDataSource();
                PDS.DataSource = DS.Tables["PDS"].DefaultView;
                return PDS;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion


        #region 执行无返回值SQL语句
        /// <summary>
        /// 执行无返回值SQL语句
        /// </summary>
        /// <param name="_SQLstr">SQL查询语句</param>
        public static void ExecuteSQL(string _SQLstr)
        {
            try
            {
                ConOpen();
                OledbCmd = new OleDbCommand(_SQLstr, OledbConn);

                OledbCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }

        }
        #endregion


        #region 执行返回Int型值的SQL
        /// <summary>
        /// 执行返回Int型值的SQL
        /// </summary>
        /// <param name="_SQLstr">SQL查询语句</param>
        /// <returns></returns>
        public static int GetIntExecute(string _SQLstr)
        {
            try
            {
                ConOpen();
                OledbCmd = new OleDbCommand(_SQLstr, OledbConn);

                int n = Convert.ToInt32(OledbCmd.ExecuteScalar());

                return n;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }
        #endregion


        #region GetValue函数
        /// <summary>
        /// GetValue函数
        /// </summary>
        /// <param name="_TableField">查询的表名</param>
        /// <param name="_ColumnField">查询的列名</param>
        /// <param name="_Column_ID">该表的ID列名</param>
        /// <param name="_ID">参数ID</param>
        /// <returns></returns>
        public static object GetValue(string _TableName, string _ColumnField, string _Column_ID, string _ID)
        {
            try
            {
                ConOpen();

                string SQLstr_GetValue = "SELECT [" + _ColumnField + "] FROM [" + _TableName + "] WHERE [" + _Column_ID + "]=" + _ID;

                OledbCmd = new OleDbCommand(SQLstr_GetValue, OledbConn);

                object Value = OledbCmd.ExecuteScalar();

                if (Value == null)
                {
                    Value = (object)string.Empty;
                }

                return Value;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }
        #endregion


        #region GetDataSetByID函数
        /// <summary>
        /// GetDataSetByID函数
        /// </summary>
        /// <param name="_TableField">查询的表名</param>
        /// <param name="_Column_ID">该表ID名称</param>
        /// <param name="_ID">参数ID</param>
        /// <param name="_srcTable">返回的数据集名称</param>
        /// <returns></returns>
        public static DataSet GetDataSetByID(string _TableName, string _Column_ID, string _ID, string _srcTable)
        {
            try
            {
                string SQLstr_GetDataSetByID = "SELECT * FROM [" + _TableName + "] WHERE [" + _Column_ID + "]=" + _ID;

                DataSet DS_GetDataSetByID = GetDataSet(SQLstr_GetDataSetByID, _srcTable);

                return DS_GetDataSetByID;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion


        #region DeleteRowByID函数
        /// <summary>
        /// DeleteRowByID函数
        /// </summary>
        /// <param name="_TableName">操作的表名</param>
        /// <param name="_Column_ID">该表ID列的名称</param>
        /// <param name="_ID">参数ID</param>
        public static void DeleteRowByID(string _TableName, string _Column_ID, string _ID)
        {
            try
            {
                string SQLstr_DeleteRowByID = "DELETE FROM [" + _TableName + "] WHERE [" + _Column_ID + "]=" + _ID;

                ConOpen();

                OledbCmd = new OleDbCommand(SQLstr_DeleteRowByID, OledbConn);

                OledbCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }
        #endregion


        #region 双SQL语句事务操作
        /// <summary>
        /// 双SQL语句事务操作
        /// </summary>
        /// <param name="_SQLstr1">执行的SQL语句1</param>
        /// <param name="_SQLstr2">执行的SQL语句2</param>
        public static void TwoSQLstrTransExecute(string _SQLstr1, string _SQLstr2)
        {
            try
            {
                ConOpen();
                OledbCmd = new OleDbCommand();
                OledbCmd.Connection = OledbConn;
                OledbTrans = OledbConn.BeginTransaction();
                OledbCmd.Transaction = OledbTrans;

                OledbCmd.CommandText = _SQLstr1;
                OledbCmd.ExecuteNonQuery();
                OledbCmd.CommandText = _SQLstr2;
                OledbCmd.ExecuteNonQuery();
                OledbTrans.Commit();
            }
            catch (Exception ex)
            {
                OledbTrans.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }
        #endregion


        #region 执行多SQL语句事务操作
        /// <summary>
        /// 执行多SQL语句事务操作
        /// </summary>
        /// <param name="_SQLstrList">执行的SQLstr语句泛型数组</param>
        public static void ManySQLstrTransExecute(List<String> _SQLstrList)
        {
            try
            {
                ConOpen();
                OledbCmd = new OleDbCommand();
                OledbCmd.Connection = OledbConn;
                OledbTrans = OledbConn.BeginTransaction();
                OledbCmd.Transaction = OledbTrans;

                for (int i = 0; i < _SQLstrList.Count; i++)
                {
                    string SQLstr = _SQLstrList[i];

                    if (SQLstr.Trim().Length > 0)
                    {
                        OledbCmd.CommandText = SQLstr;
                        OledbCmd.ExecuteNonQuery();
                    }
                }

                OledbTrans.Commit();
            }
            catch (Exception ex)
            {
                OledbTrans.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }
        #endregion


        #region 根据bool条件执行SQL函数
        /// <summary>
        /// 根据bool条件执行SQL函数
        /// </summary>
        /// <param name="_BL">参数布尔值，true则执行事务，false则取消事务</param>
        /// <param name="_SQLstr">执行的SQL语句</param>
        public static void ConditionTrans(bool _BL, string _SQLstr)
        {
            try
            {
                ConOpen();
                OledbTrans = OledbConn.BeginTransaction();
                OledbCmd = new OleDbCommand(_SQLstr, OledbConn);
                OledbCmd.Transaction = OledbTrans;
                OledbCmd.ExecuteNonQuery();

                if (_BL)
                {
                    OledbTrans.Commit();
                }
                else
                {
                    OledbTrans.Rollback();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }
        #endregion


        #region 互换字段值函数
        /// <summary>
        /// 互换字段值函数
        /// </summary>
        /// <param name="_TableName">操作的表名</param>
        /// <param name="_ColumnField">操作的列名</param>
        /// <param name="_ID_1">需交换的记录一主键</param>
        /// <param name="ID_2">需交换的记录二主键</param>
        public static void ConvertValue(string _TableName, string _ColumnField, string _IDField, string _ID_1, string _ID_2)
        {
            try
            {
                object Value1 = (object)GetValue(_TableName, _ColumnField, _IDField, _ID_1);
                object Value2 = (object)GetValue(_TableName, _ColumnField, _IDField, _ID_2);

                string SQLstr_Set1 = "UPDATE [" + _TableName + "] SET [" + _ColumnField + "]=" + Value2 + "";
                SQLstr_Set1 += " WHERE [" + _IDField + "]=" + _ID_1;

                string SQLstr_Set2 = "UPDATE [" + _TableName + "] SET [" + _ColumnField + "]=" + Value1 + "";
                SQLstr_Set2 += " WHERE [" + _IDField + "]=" + _ID_2;

                TwoSQLstrTransExecute(SQLstr_Set1, SQLstr_Set2);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion
    }
}
